package com.ima.common.repository.utils;

import org.springframework.data.domain.Sort;

import java.util.*;

/**
 * Created by lichengqi on 2017/4/6.
 * Desc
 */
public class QueryUtils {

    private static final String FORMAT_CONDITION_STRING = " %s %s :%s";

    private static final String PAGE_CONDITION_STRING = " limit :offset,:size";

    /**
     * 通过搜索条件来组装sql，通过参数：fileName的方式
     *
     * @param sql
     * @param list
     * @return
     */
    public static String generateQueryString(String sql, List<SearchFilter> list) {
        StringBuilder sb = new StringBuilder(sql);
        sql = sql.replaceAll("\\(.*\\)\\s+as[\\s+(\\S)\\s+]", "");
        if (sql.matches(".*((W|w)(H|h)(E|e)(R|r)(E|e))+.+")) {
            sb.append(" AND ");
        } else {
            sb.append(" WHERE ");
        }
        if (list != null && !list.isEmpty()) {
            Map<String, Object> map = new HashMap<String, Object>();
            int index = 1;
            for (SearchFilter field : list) {
                //如果是OR的话
                if (field.getOp().equals(OP.OR)) {
                    sb.append(generateOrString(field, map));
                } else {
                    sb.append(formatSql(field, map, index));
                }
                index++;
                sb.append(" AND ");

            }
        }
        sb.append(" 1 = 1");
        return sb.toString();
    }


    /**
     *
     * @param field
     * @param map
     * @return
     */
    public static String generateOrString(SearchFilter field, Map<String, Object> map) {
        StringBuilder sb = new StringBuilder();

        String op = getOptionSymbol(field.getOp());
        List<SearchFilter> orList = (List<SearchFilter>) field.getValue();

        if(map == null) map = new HashMap<String, Object>();

        sb.append("(");
        int index = 1;
        int len = orList.size();
        for (SearchFilter second : orList) {
            sb.append(formatSql(second, map, index));
            if (index < len) {
                sb.append(" ").append(op).append(" ");
            }
            index++;
        }
        sb.append(")");

        return sb.toString();
    }

    /**
     * @param field
     * @param map
     * @return
     */
    public static String formatSql(SearchFilter field, Map<String, Object> map, int paramIndex) {

        if(map == null) map = new HashMap<String, Object>();

        String mapKey = getParamKey(map, field.getFieldName(), getOptionSymbol(field.getOp())) + paramIndex;

        map.put(mapKey, field.getOp());

        String queryName = getDisposeQueryField(field.getFieldName());
        String str =  String.format(FORMAT_CONDITION_STRING, queryName, getOptionSymbol(field.getOp()), mapKey);
        return str;
    }


    /**
     * 获取总计路条数的sql //TODO优化
     *
     * @param sql
     * @param list
     * @return
     */
    public static String getQueryCountString(String sql) {
        StringBuffer sb = new StringBuffer(sql.length() + 20);
        sb.append("select count(1) as c from (").append(sql).append(") t");
        return sb.toString();
    }

    /**
     * 获取排序的sql
     *
     * @param sort
     * @return
     */
    public static String getOrderCondition(Sort sort) {
        if (sort == null) return "";
        String orderStr = sort.toString();
        StringBuilder sb = new StringBuilder(sort.toString().length() + 10);
        sb.append(" ORDER BY ").append(orderStr.replaceAll(":", ""));
        return sb.toString();
    }

    /**
     * 通过搜索条件返回params
     *
     * @param list
     * @return
     */
    public static Map<String, Object> convertToSqlCommandParam(SearchFilter field, Map<String, Object> map, int paramIndex) {
        if (field == null) return map;

        Object value = field.getValue();
        OP op = field.getOp();

        if (OP.LIKE.equals(op)) {
            value = "%" + value + "%";
        } else if (OP.PLIKE.equals(op)) {
            value = "%" + value;
        } else if (OP.ALIKE.equals(op)) {
            value = value + "%";
        } else if(OP.IN.equals(op)) {
            if (value instanceof String) {
                value = Arrays.asList(((String) value).split(","));
            } else if (!(value instanceof Collection)) {
                throw new IllegalArgumentException("value 格式必须为\"1,2\" 或者类型为List");
            }
        }

        String mapKey = getParamKey(map, field.getFieldName(), getOptionSymbol(field.getOp())) + paramIndex;

        map.put(mapKey, value);

        return map;
    }

    /**
     * 通过搜索条件返回params
     *
     * @param list
     * @return
     */
    public static Map<String, Object> convertToSqlCommandParam(List<SearchFilter> list) {
        Map<String, Object> map = new HashMap<>();
        int index = 1;
        for (SearchFilter filter : list) {
            Object value = filter.getValue();
            if (value instanceof List) {
                List<SearchFilter> orList = (List<SearchFilter>) filter.getValue();
                for (SearchFilter second : orList) {
                    convertToSqlCommandParam(second, map, index);
                    index++;
                }
            } else {
                convertToSqlCommandParam(filter, map, index);
                index++;
            }
        }
        return map;
    }

    /**
     *
     * @param map
     * @param fieldName
     * @param opName
     * @return
     */
    private static String getParamKey(Map<String, Object> map, String fieldName, String opName) {
        String key = getPointToUnderline(fieldName);
        if (map.containsKey(key)) {
            return opName + fieldName;
        } else {
            return fieldName;
        }
    }

    /**
     * 处理有别名的字段驼峰转下划线 tnc.tenantCode > tnc.TENANT_CODE
     *
     * @param camlName
     * @return
     */
    private static String getDisposeQueryField(String fieldName) {
        StringBuilder sb = new StringBuilder();
        int index = fieldName.indexOf(".");
        if (index > 0) {
            String alias = fieldName.substring(0, index + 1);
            String name = fieldName.substring(index + 1);
            sb.append(alias).append(camelToUnderline(name));
        } else {
            return camelToUnderline(fieldName);
        }
        return sb.toString();
    }

    /**
     * 把字段的点替换成下划线
     *
     * @param fieldName
     * @return
     */
    private static String getPointToUnderline(String fieldName) {
        if (fieldName == null || "".equals(fieldName.trim())) {
            return "";
        }
        return fieldName.replaceAll("\\.", "_");
    }

    /**
     * 通枚举值返回对应的条件字符
     * 可以考虑给枚举OP加上对应的value
     * @param op
     * @return
     */
    private static String getOptionSymbol(OP op) {
        String symbol = "";
        switch (op) {
            case GT:
                symbol = ">";
                break;
            case EQ:
                symbol = "=";
                break;
            case NE:
                symbol = "!=";
                break;
            case LT:
                symbol = "<";
                break;
            case GTE:
                symbol = ">=";
                break;
            case LTE:
                symbol = "<=";
                break;
            case ALIKE:
                symbol = "LIKE";
                break;
            case LIKE:
                symbol = "LIKE";
                break;
            case PLIKE:
                symbol = "LIKE";
                break;
            case OR:
                symbol = "OR";
                break;
            case IN:
                symbol = "IN";
                break;
            default:
                symbol = "=";
                break;
        }
        return symbol;
    }


    /**
     * 驼峰转下划线
     *
     * @param param
     * @return
     */
    public static String camelToUnderline(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_");
                sb.append(Character.toUpperCase(c));
            } else {
                sb.append(Character.toUpperCase(c));
            }
        }
        return sb.toString();
    }

    /**
     * 将下划线大写方式命名的字符串转换为驼峰式。如果转换前的下划线大写方式命名的字符串为空，则返回空字符串。
     *
     * @param name
     * @return
     */
    public static String underlineToCamel(String name) {
        StringBuilder result = new StringBuilder();
        // 快速检查
        if (name == null || "".equals(name)) {
            return "";
        } else if (!name.contains("_")) {
            return name;
        }
        String camels[] = name.split("_");
        for (String camel : camels) {
            if (camel.isEmpty()) {
                continue;
            }
            if (result.length() == 0) {
                // 第一个驼峰片段，全部字母都小写
                result.append(camel.toLowerCase());
            } else {
                // 其他的驼峰片段，首字母大写
                result.append(camel.substring(0, 1).toUpperCase());
                result.append(camel.substring(1).toLowerCase());
            }
        }
        return result.toString();
    }

    public static void main(String[] args) {
        String sql = "select * from (select * from table where 1=1) as t left join (select * from table where 1=1) as t2 on t1.id = t2.id";
        System.out.println(generateQueryString(sql, null));
    }
}
